/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.teachassistdbservice.excelutil;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 *
 * @author dusin
 */
public class ProcessExcel {
    /**
     * 
     * @param stream
     * @param sheetNo
     * @param scoreColIndex  参考分数所在列
     * @param start   起始列号
     * @param size    长度
     */
    public static void processKaocha(InputStream stream, int sheetNo, int scoreColIndex, int start,
            int size) {
        Random random = new Random();
        try {
            //Workbook wb = new HSSFWorkbook(stream);
            Workbook wb = WorkbookFactory.create(stream);
            Sheet sheet = wb.getSheetAt(sheetNo);
            int nullCount = 0; //如果出现多次空值，则停止循环
            int r = 2;
            while (nullCount < 10) {
                Row row = sheet.getRow(r++);
                Cell cell;
                String value;
                try {
                    cell = row.getCell(scoreColIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    value = getStringValue(cell).trim();
                } catch (Exception e) {
                    cell = null;
                    value = null;
                }

                if (value == null || "".equals(value)) {
                    nullCount++;
                    continue;
                }
                nullCount = 0;
                if ("优".equals(value)) {
                    for (int i = start; i < start + size; i++) {
                        double f = random.nextDouble();
                        if (f < 0.1) {
                            row.createCell(i).setCellValue(85);
                        } else {
                            row.createCell(i).setCellValue(95);
                        }
                    }
                } else if ("良".equals(value)) {
                    for (int i = start; i < start + size; i++) {
                        double f = random.nextDouble();
                        if (f < 0.1) {
                            row.createCell(i).setCellValue(75);
                        } else if (f > 0.9) {
                            row.createCell(i).setCellValue(95);
                        } else {
                            row.createCell(i).setCellValue(85);
                        }
                    }
                } else if ("中".equals(value)) {
                    for (int i = start; i < start + size; i++) {
                        double f = random.nextDouble();
                        if (f < 0.1) {
                            row.createCell(i).setCellValue(85);
                        } else {
                            row.createCell(i).setCellValue(75);
                        }
                    }

                } else if ("及格".equals(value)) {
                    for (int i = start; i < start + size; i++) {
                        double f = random.nextDouble();
                        if (f < 0.01) {
                            row.createCell(i).setCellValue(85);
                        } else {
                            row.createCell(i).setCellValue(75);
                        }
                    }
                } else if ("不及格".equals(value)) {
                    for (int i = start; i < start + size; i++) {
                        double f = random.nextDouble();
                        row.createCell(i).setCellValue(75);
                    }
                }
            }
            // Write the output to a file

            OutputStream fileOut = new FileOutputStream("src/workbook.xls");
            wb.write(fileOut);
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    private static String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType ct = cell.getCellType();
        if (CellType.STRING.equals(ct)) {
            return (cell.getRichStringCellValue().getString().trim());
        } else if (CellType.NUMERIC.equals(ct)) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString();
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        } else if (CellType.BOOLEAN.equals(ct)) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (CellType.FORMULA.equals(ct)) {
            return cell.getCellFormula().toString();
        } else {
            return null;
        }

    }
}
